package com.group.weekmgr;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.group.util.JdbcUtil;

public class CodeMgr {
	
	Connection conn;
	PreparedStatement pstmt;
	ResultSet rs;
	DataSource ds;
	
	public CodeMgr() {
		try {
			//	pooling 처리 : dbcp
			Context context = new InitialContext();
			ds = (DataSource)context.lookup("java:comp/env/jdbc/Oracle10g");
		} catch (Exception e) {
			System.out.println("연결 오류 : " + e.getMessage());
			return;
		}
	}
	
	public ArrayList<CodeBean> getCodeList(String type, String word){
		
		System.out.println(type+" "+word);
		ArrayList<CodeBean> list = new ArrayList<CodeBean>();
		String sql = "SELECT codeno, code, customer_name FROM mycode";
		try {
			conn = ds.getConnection();
			
			if(word == null){
				pstmt = conn.prepareStatement(sql);
			}else{	//	검색인 경우
				sql += " where " + type + " like ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, "%"+word+"%");
			}
			rs = pstmt.executeQuery();
			while(rs.next()){
				CodeBean bean = new CodeBean();
				bean.setCodeno(rs.getInt("codeno"));
				bean.setCode(rs.getString("code"));
				bean.setCustomerName(rs.getString("customer_name"));
				list.add(bean);
			}
		} catch (Exception e) {
			System.out.println("getCodeList 오류 : " + e.getMessage());
		}finally{
			JdbcUtil.close(rs);
			JdbcUtil.close(pstmt);
			JdbcUtil.close(conn);
		}
		return list;
	}
	
	/**
	 * 입력받은 코드넘버를 DB에서 조회하여 해당 내역을 리턴한다.
	 * @param no 코드넘버들
	 * @return 조회한 내역 리스트
	 */
	public ArrayList<CodeBean> addPerformMyCode(String[] no){
		ArrayList<CodeBean> list = new ArrayList<CodeBean>();
		try {
			int codeno = 0;
			for(int i=0; i<no.length; i++){
				codeno = Integer.parseInt(no[i]);
				String sql = "select * from mycode where codeno="+codeno;
	
				conn = ds.getConnection();
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				while(rs.next()){
					CodeBean bean = new CodeBean();
					bean.setCodeno(rs.getInt("codeno"));
					bean.setCode(rs.getString("code"));
					bean.setCustomerName(rs.getString("customer_name"));
					list.add(bean);
				}
			}
		} catch (Exception e) {
			System.out.println("addPerformMyCode 오류 : " + e.getMessage());
		}finally{
			JdbcUtil.close(rs);
			JdbcUtil.close(pstmt);
			JdbcUtil.close(conn);
		}
		return list;
	}
}
